﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Model;
using System.Data.SQLite;

namespace Data
{
    public class habitacionDataAccess
    {
        private SQLiteConnection con;
        private DataTable dt = new DataTable();
        private DataSet dataSet = new DataSet();

        public habitacionDataAccess()
        {
            con = DataBaseHelper.GetConnection("BDHostal.sqlite");
        }
        public bool existeHabitacion(string num)
        {
            string cmd = "SELECT * FROM habitacion WHERE numero = '" + num + "'";
            bool resultado=false;
         
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                    if (dt.Rows.Count>0)
                        resultado = true;
                    else
                        resultado = false;

                    
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
                return resultado;
        }

        public void NuevaHabitacion(habitacion h)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "INSERT INTO habitacion(numero,tipo,capacidad,state) VALUES ('" + h.Numero + "','" + h.Tipo + "','" + h.Capacidad + "','1')";
                    cmd.ExecuteNonQuery();  

                }
                catch (SQLiteException ex)
                {

                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Registrando Habitacion.");
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }
        public void modificarHabitacion(habitacion h)
        {
            if (existeHabitacion(h.Numero) && cambioNumero(h))
                throw new Exception("Numero ya asignado!");
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "UPDATE habitacion SET numero='" + h.Numero + "',tipo='" + h.Tipo + "',capacidad='" + h.Capacidad + "' WHERE rowid='" + h.id + "'";
                    cmd.ExecuteNonQuery();

                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Modificando Habitacion.");
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        private bool cambioNumero(habitacion h)
        {
            string cmd = "SELECT * FROM habitacion WHERE numero='" + h.Numero +"' AND rowid = '" + h.id + "'";
            bool resultado = false;

            try
            {
                con.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                dataSet.Reset();
                dataAdapter.Fill(dataSet);
                dt = dataSet.Tables[0];
                if (dt.Rows.Count > 0)
                    resultado = false;
                else
                    resultado = true;


            }
            catch (SystemException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return resultado;
        }
        public DataTable buscarHabitacionesParams(string hab)
        {
            string cmd = "SELECT numero,tipo FROM habitacion WHERE state=1 AND (numero like '%" + hab.ToLowerInvariant() + "%' OR tipo like '%" + hab.ToLowerInvariant() + "%')";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }
        public DataTable buscarHabitaciones()
        {
            string cmd = "SELECT rowid,numero,tipo FROM habitacion WHERE state=1";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable buscarHabitacionNum(string hab)
        {
            string cmd = "SELECT rowid,numero,tipo,capacidad FROM habitacion WHERE numero=" + hab.ToLowerInvariant();
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable buscarHabitacionesDeshabilitadas()
        {
            string cmd = "SELECT numero,tipo FROM habitacion WHERE state=0";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public void deshabilitarHabitacion(string h)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "UPDATE habitacion SET state='0' WHERE numero='" + h + "'";
                    cmd.ExecuteNonQuery();

                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Deshabilitando Habitacion.");
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        public DataTable buscartipoHabitaciones()
        {
            string cmd = "SELECT DISTINCT tipo FROM habitacion WHERE state=1";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable obtenerTipoHabitacion(string numeroHabitacion)
        {
            string cmd = "SELECT tipo FROM habitacion WHERE state=1 AND numero='"+numeroHabitacion+"'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public string obtenerCapacidad(string numeroHabitacion)
        {
            string cmd = "SELECT capacidad FROM habitacion WHERE state=1 AND numero='" + numeroHabitacion + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt.Rows[0].ItemArray.GetValue(0).ToString();
        }

        public DataTable buscarHabitacionesCapacidad(string tipo)
        {
            string cmd = "SELECT numero,tipo,capacidad FROM habitacion WHERE state=1 AND tipo='"+tipo+"'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        //public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=HostalDataBase;Integrated Security=true";
        //private SqlConnection cnn;

        //public Boolean existeHabitacion(string num)
        //{
        //    if (num.Equals(string.Empty))
        //    {
        //        throw new ArgumentException("Invalid Input Parameters");
        //    }
        //    //ado.net
        //    cnn = new SqlConnection(ConnString);
        //    try
        //    {
        //        cnn = new SqlConnection(ConnString);
        //        string sqlSentence = "SELECT * FROM [habitacion] WHERE [numero_habitacion] =";
        //        sqlSentence += "@num";

        //        SqlCommand cmd = cnn.CreateCommand();
        //        cmd.CommandText = sqlSentence;
        //        cmd.CommandType = System.Data.CommandType.Text;

        //        cmd.Parameters.AddWithValue("@num", num);
        //        cnn.Open();
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        System.Console.WriteLine(cmd.ToString());
        //        if (reader.HasRows)
        //        {
        //            return true;
        //        }
        //        else { return false; }
        //    }
        //    catch (Exception)
        //    {
        //        throw new ArgumentException("Error creating a room");
        //    }
        //    finally
        //    {
        //        if (cnn.State == System.Data.ConnectionState.Open)
        //        {
        //            cnn.Close();
        //        }
        //    }
        //}

        //public void NuevaHabitacion(habitacion h)
        //{
        //    if (h != null)
        //    {
        //        throw new ArgumentException("Invalid Input Parameters");
        //    }
        //    //ado.net
        //    cnn = new SqlConnection(ConnString);
        //    string sqlSentence = @"INSERT INTO [HostalDataBase].[dbo].[habitacion]([tipo],[capacidad_camas],[numero_habitacion])VALUES (@tip,@cap,@num)";
        //    SqlCommand cmd = cnn.CreateCommand();
        //    cmd.CommandText = sqlSentence;
        //    cmd.CommandType = CommandType.Text;

        //    cmd.Parameters.AddWithValue("@num", h.Numero);
        //    cmd.Parameters.AddWithValue("@tip", h.Tipo);
        //    cmd.Parameters.AddWithValue("@cap", h.Capacidad);
        //    try
        //    {
        //        cnn.Open();
        //        int value = cmd.ExecuteNonQuery();
        //        if (value < 1)
        //        {
        //            throw new ArgumentException("The record wasn't registered");
        //        }

        //    }
        //    catch (Exception e)
        //    {
        //        throw new ArgumentException("Error creating a user", e);

        //    }
        //    finally
        //    {
        //        if (cnn.State == System.Data.ConnectionState.Open)
        //        {
        //            cnn.Close();
        //        }
        //    }
        //}
        #region Miembros de IDisposable

        //public void Dispose()
        //{
        //    Dispose(true);
        //    GC.SuppressFinalize(this);
        //}
        //protected virtual void Dispose(bool disposing)
        //{
        //    if (disposing)
        //    {
        //        if (cnn != null)
        //        {
        //            cnn.Dispose();
        //            cnn = null;
        //        }
        //    }
        //}

        #endregion


        public DataTable buscarHabitacionesOcupadas()
        {
            string cmd = "SELECT DISTINCT h.rowid,h.numero,h.tipo FROM habitacion h,hospedajes hp WHERE activo='si' AND numeroHabitacion=numero";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                 
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;        
        }
    }
}
